import pymysql


host = 'localhost'
port = 3308
user = 'root'
password = '123456'
database = 'dbtext'
charset = 'utf8'
# 创建数据库的连接对象，连接数据库
db = pymysql.Connect(host=host, port=port, user=user, password=password, database=database, charset=charset)
print('数据库已连接')
# 创建游标对象
cursor = db.cursor()
# 增
# 准备sql语句
name = 'lisi'
pwd = '123'
sql = f"insert into text values(null,'{name}','{pwd}')    "
try:
    cursor.execute(sql)
    db.commit() #事务提交
    print('数据已插入')
except Exception as e:
    db.rollback()   #事务回滚
    print(e)

# 改
name  = 'lisi'
pwd = 111
sql = f"update text set PASSWORD = '{pwd}' where NAME='{name}'"
try:
    cursor.execute(sql)
    db.commit() #事务提交
    print('数据已修改')
except Exception as e:
    db.rollback()   #事务回滚
    print(e)
# 删
username = 'lisi'
sql = f"delete from text where NAME='{username}'"
try:
    cursor.execute(sql)
    db.commit() #事务提交
    print('数据已删除')
except Exception as e:
    db.rollback()   #事务回滚
    print(e)
# 查
sql = f"select*from text"
try:
    rows = cursor.execute(sql)
    data = cursor.fetchall()
    print('数据已查询')
except Exception as e:
    print(e)
# 查询了几行
print(rows)
# 打印一下，记录
for row in data:
    print(f'{row[1]}------{row[2]}')
# 关闭游标
cursor.close()
# 关闭数据库
db.close()